/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2018/12/3 14:41:13                           */
/*==============================================================*/
/* 新增如下数据表：1.Posts, 2.AreaMerger, 3.SectorMerger 4.Discharge*/

CREATE DATABASE /*!32312 IF NOT EXISTS*/`discharge` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `discharge`;
set foreign_key_checks=0;

drop table if exists Area;

drop table if exists AreaMerger;

drop table if exists DataDate;


drop table if exists Pollutants;

drop table if exists Posts;

drop table if exists Sector;

drop table if exists SectorMerger;

drop table if exists Users;


drop table if exists Tech;

drop table if exists Ef;

drop table if exists Remv;

drop table if exists Discharge;

drop table if exists UploadRecord;


-- ----------------------------
-- table structure for user
-- ----------------------------
drop table if exists `user`;
CREATE TABLE user (
  `id` int  primary key auto_increment,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `question` varchar(100) DEFAULT NULL,
  `answer` varchar(100) DEFAULT NULL,
  `role` int NOT NULL,
  `realName` varchar(50),   ---新增字段
  `company` varchar(200),  ----新增字段
  `department` varchar(200),  ----新增字段
  `jobs`       varchar(150), ------新增字段
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  UNIQUE KEY `user_name_unique` (`username`) USING BTREE
);

-- ----------------------------
--  Records of `user`
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES ('1', 'admin', '427338237BD929443EC5D48E24FD2B1A', 'admin@407.com', '407', '问题', '答案', '1', now(), now());
COMMIT;

/*==============================================================*/
/* Table: Posts                                                 */
/*==============================================================*/
create table Posts
(
   postId               int primary key auto_increment,
   userId               int,
   title                varchar(300),
   contents             text,
   receiver             varchar(150),---新增字段
   fileUrl              varchar(500),---新增字段
   orderNo              int default(1),  ---新增字段，0 代表最高优先级（置顶）--默认为1，普通优先级
   postType             int default(0),   -- 新增字段，0默认为通知公告，1代表用户提交问题待解决，2代表用户提交信息已解决
   postDate             datetime
);



create table UploadRecord    --新增表结构
(
    upReId          int,
    fileType        varchar(150),
    fileName        varchar(300),
    fileUrl         varchar(500),---新增字段
    createUserId    int,
    createTime      datetime
)

/*==============================================================*/
/* Table: Area                                                  */
/*==============================================================*/
create table Area
(
   areaId               int primary key auto_increment,
   areaAb               varchar(150),  
   areaName             varchar(200),  
   areaLevel            int not null,  -- 1 代表省级，2 代表市是市级，3 代表区级 ....
   supAreaId            int not null,   -- 0 代表省级，代表上级地区的编号   -1 代表地区聚合。
   createUserId         int not null --新增字段
);

/*
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('quanguo','全国',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('beijing','北京',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('tianjin','天津',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('hebei','河北',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('heilongjiang','黑龙江',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('jilin','吉林',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('liaoning','辽宁',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('neimenggu','内蒙古',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('shaanxi','陕西',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('shanxi','山西',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('henan','河南',1,0);
insert into Area(areaAb,areaName,areaLevel,supAreaId) values('gansu','甘肃',1,0);
*/
/*==============================================================*/
/* Table: AreaMerger  地区聚合                                                 */
/*==============================================================*/
create table AreaMerger
(
   merId               int primary key auto_increment,
   merAb               varchar(150),  -- 京津冀，珠三角       
   merName             varchar(200),  -- 京津冀，珠三角    
   subAreaId           int not null,   -- areaId
   createUserId         int not null --新增字段
);


/*==============================================================*/
/* Table: DataDate                                              */
/*==============================================================*/
create table DataDate
(
   dateId               int primary key auto_increment,
   year                 varchar(5) not null,
   month                varchar(3),
   day                  varchar(3),
   createUserId         int not null --新增字段
);

insert into DataDate(year,month,day) values('2014','','');
insert into DataDate(year,month,day) values('2015','','');
insert into DataDate(year,month,day) values('2016','','');
insert into DataDate(year,month,day) values('2017','','');
insert into DataDate(year,month,day) values('2018','','');
insert into DataDate(year,month,day) values('2019','','');
insert into DataDate(year,month,day) values('2020','','');
/*==============================================================*/
/* Table: Pollutants                                            */
/*==============================================================*/
create table Pollutants
(
   polId                int primary key auto_increment,
   pollutantAb          varchar(50) not null,
   pollutantName        varchar(50),
   supPollutant         int not null, -- 所属污染物编号
   createUserId         int not null --新增字段
);
/*
insert into Pollutants(pollutantAb, pollutantName, supPollutant) values('NOX','氮氧化物',0);
insert into Pollutants(pollutantAb, pollutantName, supPollutant) values('PM','PM',0);
insert into Pollutants(pollutantAb, pollutantName, supPollutant) values('SO2','二氧化硫',0);
insert into Pollutants(pollutantAb, pollutantName, supPollutant) values('VOC','挥发性有机物',0);
insert into Pollutants(pollutantAb, pollutantName, supPollutant) values('PM2.5','PM2.5',2);
insert into Pollutants(pollutantAb, pollutantName, supPollutant) values('PM2.5-10','PM2.5-10',2);
insert into Pollutants(pollutantAb, pollutantName, supPollutant) values('PM10','PM10',2);
*/

/*==============================================================*/
/* Table: Posts                                                 */
/*==============================================================*/
/*create table Posts
(
   postId               int primary key auto_increment,
   userId               int,
   title                varchar(300),
   contents             text,
   postDate             datetime
);
*/
/*==============================================================*/
/* Table: Sector                                                */
/*==============================================================*/
create table Sector
(
   secId                int primary key auto_increment,
   secAb                varchar(100),
   secName              varchar(200),
   secLevel             int not null, -- 部门等级， 1 一级部门，2 二级部门，3 三级部门
   supSecId             int not null, -- 0 代表最高级部门，没有上级部门，
   createUserId         int not null --新增字段
);
/*
insert into Sector(secAb,secName,secLevel,supSecId) values('PP','电力name',1,0);
insert into Sector(secAb,secName,secLevel,supSecId) values('HEAT','供热',1,0);
insert into Sector(secAb,secName,secLevel,supSecId) values('IND','工业',1,0);
insert into Sector(secAb,secName,secLevel,supSecId) values('DOM','民用',1,0);
insert into Sector(secAb,secName,secLevel,supSecId) values('TRA','交通',1,0);
insert into Sector(secAb,secName,secLevel,supSecId) values('OTH','其他',1,0);
insert into Sector(secAb,secName,secLevel,supSecId) values('VOCP','VOC工业过程',1,0);
*/
/*==============================================================*/
/* Table: sectorMerger  地区聚合                                                 */
/*==============================================================*/
create table SectorMerger
(
   secMerId               int primary key auto_increment,
   secMerAb               varchar(150),  --       
   secMerName             varchar(200),  -- 民用化石|| 民用生物
   subSecId               int not null,   -- secId
   createUserId           int not null --新增字段
);

/*==============================================================*/
/* Table: baseData                                              */
/*==============================================================*/
create table Tech
(
   techId               int  primary key auto_increment,
   secId                int,
   areaId               int,
   polId                int,
   dateId               int,
   scenario             varchar(100) not null,
   fuel                 varchar(150),
   unit                 varchar(100),
   param                float,
   createUserId         int not null,
   createTime           datetime
);

/*
insert into Tech(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,1,1,1,'base','MD','Wt',25.45,1,20181220);
insert into Tech(secId,areaId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,2,1,'base','MD','Wt',34.45,1,20181220);
insert into Tech(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,3,1,1,'base','MD','Wt',67.45,1,20181220);
insert into Tech(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,4,1,1,'base','MD','Wt',45.45,1,20181220);
insert into Tech(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,1,1,1,'base','MD','Wt',54.45,1,20181220);
insert into Tech(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,2,1,1,'base','MD','Wt',67.45,1,20181220);
insert into Tech(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,3,1,1,'base','MD','Wt',89.45,1,20181220);
*/
create table Ef
(
   efId               int  primary key auto_increment,
   secId                int,
   areaId               int,
   polId                int,
   dateId               int,
   scenario             varchar(100) not null,
   fuel                 varchar(150),
   unit                 varchar(100),
   param                float,
   createUserId         int not null,
   createTime           datetime
);
/*
insert into Ef(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,1,1,1,'base','MD','Wt',65.45,1,20181220);
insert into Ef(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,2,1,1,'base','MD','Wt',45.45,1,20181220);
insert into Ef(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,2,2,1,'base','MD','Wt',34.45,1,20181220);
insert into Ef(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,4,1,1,'base','MD','Wt',23.45,1,20181220);
insert into Ef(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,1,1,1,'base','MD','Wt',54.45,1,20181220);
insert into Ef(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,2,1,1,'base','MD','Wt',76.45,1,20181220);
insert into Ef(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,3,1,1,'base','MD','Wt',53.45,1,20181220);
*/

create table Remv
(
   remvId               int  primary key auto_increment,
   secId                int,
   areaId               int,
   polId                int,
   dateId               int,
   scenario             varchar(100) not null,
   fuel                 varchar(150),
   unit                 varchar(100),
   param                float,
   createUserId         int not null,
   createTime           datetime
);
/*
insert into Remv(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,1,1,1,'base','MD','Wt',34.45,1,20181220);
insert into Remv(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,2,1,1,'base','MD','Wt',45.45,1,20181220);
insert into Remv(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,2,2,1,'base','MD','Wt',76.45,1,20181220);
insert into Remv(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(1,4,1,1,'base','MD','Wt',34.45,1,20181220);
insert into Remv(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,1,1,1,'base','MD','Wt',32.45,1,20181220);
insert into Remv(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,2,1,1,'base','MD','Wt',96.45,1,20181220);
insert into Remv(secId,areaId,PolId,dateId,scenario,fuel,unit,param,createUserId,createTime) values(2,3,1,1,'base','MD','Wt',68.45,1,20181220);
*/


create table Discharge
(
   disId               int  primary key auto_increment,
   secId                int,
   areaId               int,
   polId                int,
   dateId               int,
   scenario             varchar(100) not null,
   fuel                 varchar(150),
   unit                 varchar(100),
   disType				varchar(50),
   param                float,
   createUserId         int not null,
   createTime           datetime
);
insert into Discharge(secId,areaId,PolId,dateId,scenario,fuel,unit,disType,param,createUserId,createTime) values(1,1,1,1,'base','MD','Wt','UCD',34.45,1,20181220);
insert into Discharge(secId,areaId,PolId,dateId,scenario,fuel,unit,disType,param,createUserId,createTime) values(1,2,1,1,'base','MD','Wt','UCD',45.45,1,20181220);
insert into Discharge(secId,areaId,PolId,dateId,scenario,fuel,unit,disType,param,createUserId,createTime) values(1,2,2,1,'base','MD','Wt','UCD',76.45,1,20181220);
insert into Discharge(secId,areaId,PolId,dateId,scenario,fuel,unit,disType,param,createUserId,createTime) values(1,4,1,1,'base','MD','Wt','CD',34.45,1,20181220);
insert into Discharge(secId,areaId,PolId,dateId,scenario,fuel,unit,disType,param,createUserId,createTime) values(2,1,1,1,'base','MD','Wt','CD',32.45,1,20181220);
insert into Discharge(secId,areaId,PolId,dateId,scenario,fuel,unit,disType,param,createUserId,createTime) values(2,2,1,1,'base','MD','Wt','CD',96.45,1,20181220);
insert into Discharge(secId,areaId,PolId,dateId,scenario,fuel,unit,disType,param,createUserId,createTime) values(2,3,1,1,'base','MD','Wt','CD',68.45,1,20181220);


-- ----------------------------
-- View structure for `view_UnConDis`
-- ----------------------------
/*DROP VIEW
IF EXISTS `view_UnConDis`;

CREATE ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER 
VIEW `view_UnConDis` AS (
    SELECT E.efId, E.secId, E.polId, E.areaId, E.dateId, S.secAb, S.secName, P.pollutantAb, P.pollutantName,
	A.areaAb, A.areaName, D.year, D.month, D.day, 
	E.param*T.param as unConDis
FROM Ef E
left join Sector S on S.secId = E.secId
left join Pollutants P on P.polId = E.polId
left join Area A on A.areaId = E.areaId
left join DataDate D on D.dateId = E.dateId
left join Tech T on T.secId = E.secId and T.areaId = E.areaId and (T.polId is null or T.polId = E.polId)

);

-- ----------------------------
-- View structure for `view_ConDis`
-- ----------------------------
DROP VIEW
IF EXISTS `view_ConDis`;

CREATE ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER 
VIEW `view_ConDis` AS (
    SELECT U.efId, U.secId, U.polId, U.areaId, U.dateId, U.secAb, U.secName, U.pollutantAb, U.pollutantName,
   U.areaAb, U.areaName, U.year, U.month, U.day, 
	U.unConDis*(100-R.param) as conDis
FROM view_unConDis U 
left join Remv R on R.secId = U.secId and R.polId = U.polId and R.areaId = U.areaId and R.dateId = U.dateId

);*/